package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.LinkedList;
import ModelLayer.Service;

public class DBService implements IFDBService {
	private Connection con;

	public DBService() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Service getService(int serviceId, boolean retrieveAssociation) {
		String wClause = "  serviceId = '" + serviceId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertService(Service service) {
		int nextId = GetMaxId.getMaxId("Select max(serviceId) From service") + 1;

		String query = "INSERT INTO service(serviceId, name, price, description) VALUES('"
				+ nextId
				+ "','"
				+ service.getName()
				+ "','"
				+ service.getPrice() + "','" + service.getDescription() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("service is not inserted");
		}
		return nextId;
	}

	@Override
	public LinkedList<Service> getAllService(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public int updateService(Service service) {
		Service serviceObj = service;
		int rc = -1;
		String query = "UPDATE service SET " + "name = '"
				+ serviceObj.getName() + "'," + "price = '"
				+ serviceObj.getPrice() + "'," + "description = '"
				+ serviceObj.getDescription() + "'" + " WHERE serviceId = '"
				+ serviceObj.getId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in service db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteService(int serviceId) {
		int rc = -1;

		String query = "DELETE FROM service WHERE serviceId = '" + serviceId
				+ "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in service db: " + ex);
		}
		return (rc);
	}

	private Service singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Service service = new Service();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				service = buildService(results);
				stmt.close();
			} else {
				service = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return service;
	}

	private LinkedList<Service> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		LinkedList<Service> list = new LinkedList<Service>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next()) {
				Service service = new Service();
				service = buildService(results);
				list.add(service);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Service buildService(ResultSet results) {
		Service service = new Service();
		try {
			service.setId(results.getInt("serviceId"));
			service.setName(results.getString("name"));
			service.setPrice(results.getDouble("price"));
			service.setDescription(results.getString("description"));
		} catch (Exception e) {
			System.out.println("error in building the service object");
		}
		return service;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT serviceId, name, price, description FROM service";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
